import re
from pathlib import Path
from openpyxl import load_workbook,Workbook
import pandas as pd


def process_file(file_path, name, net):
    with file_path.open(encoding='utf-16-le') as file:
        lines = file.readlines()
        sn = None
        ip = None
        mac = None
        for i, line in enumerate(lines):
            line = line.rstrip("\r\n")
            if i == 1:
                sn = line
            if "IPAddress" in line:
                ip = re.findall(r'\b(?:\d{1,3}\.){3}\d{1,3}\b', line.split("=")[1])[0]
            if "MACAddress" in line:
                mac = line.split("=")[1]
        
        return {'SN':sn, 'IP':ip, 'MAC':mac,'NET':net}

def write_to_tempfile(data_dict: dict, template_path: str):
    # 将数据写入指定的单元格
    for name, data_list in data_dict.items():
        if len(data_list) > 6:
            print(f'{name}的数据大于6项，请手动填写')
            continue

        # 打开模板 Excel 文件
        workbook = load_workbook(template_path)
        # 获取默认的工作表
        sheet = workbook.active

        for i, data in enumerate(data_list):
            # 将信息写入指定的单元格                 
            if i == 0 :
                sheet['D2'] = name       
            sheet[f'E{4+i}'] = data['SN']
            sheet[f'G{4+i}'] = data['MAC']
            sheet[f'H{4+i}'] = data['NET']
            sheet[f'I{4+i}'] = "否"

            sheet[f'E{10+i}'] = data['SN']
            sheet[f'G{10+i}'] = data['MAC']
            sheet[f'H{10+i}'] = data['NET']
            sheet[f'I{10+i}'] = "否"

            sheet[f'E{16+i}'] = data['SN']
            sheet[f'G{16+i}'] = data['MAC']
            sheet[f'H{16+i}'] = data['NET']
            sheet[f'I{16+i}'] = "是"

            sheet[f'E{22+i}'] = data['SN']
            sheet[f'G{22+i}'] = data['MAC']
            sheet[f'H{22+i}'] = data['NET']
            sheet[f'I{22+i}'] = "是"                
        # 保存 Excel 文件
        output_path = dir_path / f'{name}.xlsx'
        workbook.save(output_path)
        print(f'{name}-表格填写完成') 


def write_to_excel(data_dict: dict, exl_file: str):
    wb=Workbook()
    sheet = wb.active
    
    sheet.append(['姓名', '网络', '序列号', 'IP', 'MAC'])    
    for name, data_list in data_dict.items():
        # print(data_list)
        for data in data_list:
            sheet.append([name, data['NET'], data['SN'], data['IP'], data['MAC']])
    wb.save(exl_file)
    print(f'{exl_file}文档生成完成')

# 从收集的文件中获取姓名，网络，序列号，MAC地址，IP等信息，存入字典中
# data_dict={"姓名":[{net:接入网络1,sn:序列号1,mac:MAC地址1,ip:IP地址1},{net:接入网络2,sn:序列号2,mac:MAC地址2,ip:IP地址2}]}
# data_dict={key1:[列表1],key2:[列表2]}
def get_info_from_file(dir_list) -> dict:  # itmes 为文件列表
    # 创建一个字典来存储每个姓名对应的 Excel 文件对象
    data_dict = {}
    # 循环处理每个文件和文件夹
    for item in dir_list:
        if item.is_dir():
            name, net = item.stem.split("_")
            file_list = item.glob('*BIOS.txt')

            for file_path in file_list:
                row_info_dict = process_file(file_path, name, net)
                
                # 检查字典中是否存在该姓名的 Excel 
                if name in data_dict:
                    data_list = data_dict[name]
                else:
                    # 创建一个新的列表
                    data_list = []
                    data_dict[name] = data_list

                # 将字典添加到列表中
                data_list.append(row_info_dict)
    return data_dict    

def read_from_excle(exl_file: str) -> dict:
    # 读取Excel文件
    df = pd.read_excel(exl_file)

    # 将DataFrame转换为字典
    data = df.to_dict(orient='records')

    # 创建空字典
    result = {}

    # 遍历每一行数据
    for row in data:
        # 如果姓名已经在字典中存在，则将当前行的其他列内容添加到值列表中
        if row['姓名'] in result:
            result[row['姓名']].append({
                'NET': row['网络'],
                'SN': row['序列号'],
                'IP': row['IP'],
                'MAC': row['MAC']
            })
        # 如果姓名在字典中不存在，则创建新的键值对
        else:
            result[row['姓名']] = [{
                'NET': row['网络'],
                'SN': row['序列号'],
                'IP': row['IP'],
                'MAC': row['MAC']
            }]

    # 打印字典内容
    #print(result)
    return result


# 方法1 收集批处理文件的信息，写入到data_dict中，给后续write_to_tempfile生成模板，同时将统计信息写到excel表格中
def method1():
    # 从收集的文件中获取信息，信息存入公共变量data_dict中
    data_dict = get_info_from_file(dir_list)
    # 将信息写入excel表格中
    write_to_excel(data_dict, exl_file)
    # 将信息写入excel 模板文件中并另存为姓名的excel文件
    write_to_tempfile(data_dict, template_path)

# 方法2 从excel表中读取信息，excecl表格表头为姓名    网络  序列号 IP  MAC，读取到字典中，给write_to_tempfile生成模板
def method2():
    data_dict = read_from_excle('org_info.xlsx')
    write_to_tempfile(data_dict, template_path)


# 指定目录路径
dir_path = Path('t')

# 获取目录下的所有文件和文件夹
dir_list = dir_path.glob('*')

# 模板文件
template_path = Path('自查表-模板.xlsx')

exl_file = 'org_info.xlsx'
print('运行方法介绍：1 从批处理bat收集来的文件中自动填写模板文件，2 从excel表中填写模板文件')
chose = input('请选择运行方法：')
if chose == '1':
    method1()
elif chose == '2':
    method2()
else:
    print('没有该项选择')
